import logging

import pymysql

class DBUtil():
    # 添加类属性
    conn = None

    # 判断conn是否为空，如果是，再创建
    @classmethod
    def __get_conn(cls):
        if cls.conn is None:
            cls.conn = pymysql.connect(host="211.103.136.244", port=7061,
                                   user="student", password="iHRM_student_2021", database="ihrm", charset="utf8")

        # 返回 非空连接
        return cls.conn

    # 判断conn不为空需要关闭
    @classmethod
    def __close_conn(cls):
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None

    # 常用方法：查询一条
    @classmethod
    def select_one(cls, sql):
        res = None
        cursor = None

        try:
            # 获取连接
            cls.__get_conn()

            # 获取游标
            cursor = cls.conn.cursor()

            # 执行select语句
            cursor.execute(sql)

            # 提取一条结果
            res = cursor.fetchone()

        except Exception as err:
            # print("select语句错误：", str(err))
            logging.info("select语句错误：", str(err))
        finally:
            # 关闭游标
            cursor.close()

            # 关闭连接
            cls.__close_conn()

            return res

    # 常用方法：增删改
    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            # 获取连接
            cls.__get_conn()

            # 获取游标
            cursor = cls.conn.cursor()

            # 执行增删改语句
            cursor.execute(sql)

            # 提交事务
            cls.conn.commit()

            # print("影响的行数:", cls.conn.affected_rows())
            logging.info("影响的行数:", cls.conn.affected_rows())

        except Exception as err:
            # 回滚事务
            cls.conn.rollback()
            # print("增删改语句错误:", str(err))
            logging.error("增删改语句错误:", str(err))

        finally:
            # 关闭游标
            cursor.close()

            # 关闭连接
            cls.__close_conn()





if __name__ == "__main__":
    res = DBUtil.select_one("select * from t_book;")
    print("查询结果:", res)

    DBUtil.uid_db("update t_book set is_delete = 1 where id =1;")